/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ar.lucas.controlgasto.resources;

import ar.lucas.controlgasto.conexion.MyConnection;
import ar.lucas.controlgasto.altas.dto.AltaGastoDTO;
import ar.lucas.controlgasto.altas.dto.AltaPersonaDTO;
import ar.lucas.controlgasto.dao.ConceptoDTO;
import ar.lucas.controlgasto.dao.GastoDiaDTO;
import ar.lucas.controlgasto.dao.PersonaDAO;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author lucmussi
 */
public class Consultas {

    public static Collection<GastoDiaDTO> buscarGastosPorMes(int id_persona, Date cal) throws SQLException{
    Connection con = (Connection) MyConnection.getConnection();

    Collection<GastoDiaDTO> gastos = new ArrayList<GastoDiaDTO>();
    SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
    String currentTime = sdf.format(cal);
   /* String query = "SELECT DAY(c.datefield) AS dia, IFNULL(SUM(g.Monto),0) AS total_dia"+
                    " FROM calendario c"+
                    " LEFT JOIN gastos g ON (c.datefield = DATE(g.Fecha))"+
                    " LEFT JOIN persona p ON g.id_persona = p.id_persona"+
                    " WHERE (c.datefield BETWEEN ("+
						 "SELECT DATE_SUB('"+currentTime+"', INTERVAL (DAY('"+currentTime+"')-1) DAY))"+
						 " AND (SELECT LAST_DAY('"+currentTime+"'))"+
						 " AND IFNULL(p.id_persona,"+id_persona+") = "+id_persona+")"+
                    " GROUP BY dia";*/
    /*String query = "SELECT DAY(c.datefield) AS dia, IFNULL(SUM(g.monto),0) AS total_dia"+
                   " FROM calendario c"+
                   " LEFT JOIN gastos g ON (c.datefield = DATE(g.fecha)) AND g.id_persona="+id_persona+
                   " WHERE (c.datefield BETWEEN ('2011-11-01') AND ('2011-11-30'))"+
                   " GROUP BY dia;";*/
    String query = "SELECT DAY(c.datefield) AS dia, IFNULL(SUM(g.monto),0) AS total_dia"+
                   " FROM calendario c"+
                   " LEFT JOIN gastos g ON (c.datefield = DATE(g.fecha)) AND g.id_persona="+id_persona+
                   " WHERE (c.datefield BETWEEN (SELECT DATE_SUB('"+currentTime+"',INTERVAL (DAY('"+currentTime+"')-1)DAY))"+
						" AND (SELECT LAST_DAY('"+currentTime+"')) )"+
                   " GROUP BY dia";
    ResultSet rs;
    Statement s = (Statement) con.createStatement();
    rs = s.executeQuery(query);
    while(rs.next()){
        GastoDiaDTO gasto = new GastoDiaDTO();
        gasto.setDia(rs.getInt("dia"));
        gasto.setMonto(rs.getFloat("total_dia"));

        gastos.add(gasto);
    }
    rs.close();
    s.close();
    return gastos;
    }

    public static String buscarPersonaById(int id_persona) throws SQLException{
        Connection con = (Connection) MyConnection.getConnection();
        String query = "SELECT CAST(CONCAT(nombre,' ',apellido) AS char) AS fullname FROM persona WHERE id_persona = "+id_persona;
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        String nombre = null;
        while(rs.next()){
            nombre = rs.getString("fullname");
        }
        rs.close();
        s.close();
        return nombre;
    }

    public static Collection<PersonaDAO> buscarAllPersonas() throws SQLException{
        Connection con = (Connection) MyConnection.getConnection();
        
        String query = "SELECT * FROM persona";
        Collection<PersonaDAO> personas = new ArrayList<PersonaDAO>();
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        while(rs.next()){
            PersonaDAO persona = new PersonaDAO();
            persona.setId_persona(rs.getInt("id_persona"));
            persona.setNombre(rs.getString("nombre")+" "+rs.getString("apellido"));
            personas.add(persona);
        }
        rs.close();
        s.close();
        return personas;
    }
    
    public static Collection<ConceptoDTO> buscarAllConceptos() throws SQLException {
        Connection con = (Connection) MyConnection.getConnection();
        String query = "SELECT * FROM tipo_gasto";
        Collection<ConceptoDTO> tipo_gastos = new ArrayList<ConceptoDTO>();
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        while(rs.next()){
            ConceptoDTO tipo_gasto = new ConceptoDTO();
            tipo_gasto.setId_tipo_gasto(rs.getInt("id_tipo_gasto"));
            tipo_gasto.setDescripcion(rs.getString("descripcion"));
            tipo_gastos.add(tipo_gasto);
        }
        rs.close();
        s.close();
        return tipo_gastos;
    }
   
    
    public static Double buscarTotalMes(Integer id_persona, Integer mes) {
        Connection con = (Connection) MyConnection.getConnection();
        Double totalmes = 0.0;
        String query = "SELECT SUM(g.Monto) AS monto"+
                       " FROM gastos g"+
                       " WHERE g.id_persona="+id_persona+" AND MONTH(g.fecha) = "+mes;
        ResultSet rs;
        Statement s;
        try {
            s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            rs.next();
            totalmes = rs.getDouble("monto");
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalmes;
    }

    public static Double buscarTotalMes(Integer id_persona, Integer mes, Integer anio) {
        Connection con = (Connection) MyConnection.getConnection();
        Double totalmes = 0.0;
        String query = "SELECT SUM(g.Monto) AS monto"+
                       " FROM gastos g"+
                       " WHERE g.id_persona="+id_persona+" AND MONTH(g.fecha) = "+mes+
                       " AND YEAR(g.fecha) = "+anio;
        ResultSet rs;
        Statement s;
        try {
            s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            rs.next();
            totalmes = rs.getDouble("monto");
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalmes;
    }

    public static Double[] buscarTotalMesesAnios(Integer id_persona, Integer mes, Integer anio){
        Connection con = (Connection) MyConnection.getConnection();
        int i = 0;
        Double[] totalmeses = new Double[3];
        String query = "SELECT IFNULL(SUM(g.monto),0) AS monto"+
                       " FROM calendario c"+
                       " LEFT JOIN gastos g ON (c.datefield = DATE(g.Fecha)) AND g.id_persona = "+id_persona+
                       " WHERE (YEAR(c.datefield) BETWEEN "+(anio-2)+" AND "+anio+") AND MONTH(c.datefield) = "+mes+
                       " GROUP BY YEAR(c.datefield)";
        ResultSet rs;
        Statement s;
        try {
            s = (Statement) con.createStatement();
            rs = s.executeQuery(query);
            while(rs.next()){
                totalmeses[i] = rs.getDouble("monto");
                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalmeses;
    }

    public static Double buscarTotalAnio(Integer id_persona, Integer anio) throws SQLException{
        Connection con = (Connection) MyConnection.getConnection();
        Double totalmes = 0.0;
        String query = "SELECT SUM(g.Monto) AS monto"+
                       " FROM gastos g"+
                       " JOIN persona p ON g.id_persona = p.id_persona"+
                       " WHERE p.id_persona="+id_persona+" AND YEAR(g.fecha) = "+anio;
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        rs.next();
        totalmes = rs.getDouble("monto");
        return totalmes;
    }

    public static Double buscarMayorGastoMes(Integer id_persona, Integer mes) throws SQLException {
        Connection con = (Connection) MyConnection.getConnection();
        Double mayormes = 0.0;
        String query = "SELECT MAX(g.Monto) AS monto"+
                       " FROM gastos g"+
                       " JOIN persona p ON g.id_persona = p.id_persona"+
                       " WHERE p.id_persona="+id_persona+" AND MONTH(g.fecha) = "+mes;
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        rs.next();
        mayormes = rs.getDouble("monto");
        return mayormes;
    }

    public static String buscarDescripcionMayorGastoMes(Integer id_persona, Integer mes, Double mayorGasto) throws SQLException{
        Connection con = (Connection) MyConnection.getConnection();
        String descripcion = "";
        String query = "SELECT g.denominacion"+
                       " FROM gastos g"+
                       " JOIN persona p ON g.id_persona = p.id_persona"+
                       " WHERE p.id_persona="+id_persona+" AND MONTH(g.fecha) = "+mes+" and g.Monto = "+mayorGasto;
        ResultSet rs;
        Statement s = (Statement) con.createStatement();
        rs = s.executeQuery(query);
        rs.next();
        descripcion = rs.getString("denominacion");
        return descripcion;
    }

    public static boolean altaNuevoGasto(AltaGastoDTO alta_gasto){
        try {
            Connection con = (Connection) MyConnection.getConnection();
            Statement s = (Statement) con.createStatement();
            Date fecha = alta_gasto.getFecha();
            Format sdf = new SimpleDateFormat("yyyy-MM-dd");
            String fecha_sel = sdf.format(fecha);
            String query = "INSERT into gastos (denominacion,monto,fecha,id_tipo_gasto,id_persona)"+ 
                           " values ('" + alta_gasto.getDescripcion() + "'," + alta_gasto.getMonto() + ",'" + fecha_sel + "'," + alta_gasto.getId_tipo_gasto() + "," + alta_gasto.getId_persona() + ")";
            s.executeUpdate(query);
            s.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public static boolean altaNuevoPersona(AltaPersonaDTO alta_persona){
        try {
            Connection con = (Connection) MyConnection.getConnection();
            Statement s = (Statement) con.createStatement();
            String query = "INSERT INTO persona (nombre, apellido) values ('"+alta_persona.getNombre()+"','"+alta_persona.getApellido()+"')";
            s.executeUpdate(query);
            s.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public static boolean altaNuevoConcepto(String concepto){
        try {
            Connection con = (Connection) MyConnection.getConnection();
            Statement s = (Statement) con.createStatement();
            String query = "INSERT INTO tipo_gasto (descripcion) values ('"+concepto+"')";
            s.executeUpdate(query);
            s.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Consultas.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public static boolean modificarPersona(String query){
        try {
            Connection con = (Connection) MyConnection.getConnection();
            Statement s = (Statement) con.createStatement();
            s.executeUpdate(query);
            s.close();
            return true;
        } catch (SQLException ex) {
            //JOptionPane.showMessageDialog(null, "Error de MySQL: \n"+ex, "Error", JOptionPane.ERROR_MESSAGE);
            return false;
        }
    }
}
